﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using Moon.DBUtility;

namespace DAL
{
    public class DictionaryDAL
    {
        #region  Method
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from t_dictionary");
            strSql.Append(" where id=@id");
            OleDbParameter[] parameters = {
					new OleDbParameter("@id", OleDbType.Integer,4)
			};
            parameters[0].Value = id;

            return DbHelperOleDb.Exists(strSql.ToString(), parameters);
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public bool Add(Model.DictionaryInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into t_dictionary(");
            strSql.Append("[Name],[Values],Nodeid,ParentID,Type,ImagePath)");
            strSql.Append(" values (");
            strSql.Append("@Name,@Values,@Nodeid,@ParentID,@Type,@ImagePath)");
            OleDbParameter[] parameters = {
					new OleDbParameter("@Name", OleDbType.VarChar,50),
					new OleDbParameter("@Values", OleDbType.VarChar,255),
					new OleDbParameter("@Nodeid", OleDbType.Integer,4),
					new OleDbParameter("@ParentID", OleDbType.Integer,4),
					new OleDbParameter("@Type", OleDbType.VarChar,50),
					new OleDbParameter("@ImagePath", OleDbType.VarChar,255)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Values;
            parameters[2].Value = model.Nodeid;
            parameters[3].Value = model.ParentID;
            parameters[4].Value = model.Type;
            parameters[5].Value = model.ImagePath;

            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.DictionaryInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update t_dictionary set ");
            strSql.Append("[Name]=@Name,");
            strSql.Append("[Values]=@Values,");
            strSql.Append("Nodeid=@Nodeid,");
            strSql.Append("ParentID=@ParentID,");
            strSql.Append("Type=@Type,");
            strSql.Append("ImagePath=@ImagePath");
            strSql.Append(" where id=@id");
            OleDbParameter[] parameters = {
					new OleDbParameter("@Name", OleDbType.VarChar,50),
					new OleDbParameter("@Values", OleDbType.VarChar,255),
					new OleDbParameter("@Nodeid", OleDbType.Integer,4),
					new OleDbParameter("@ParentID", OleDbType.Integer,4),
					new OleDbParameter("@Type", OleDbType.VarChar,50),
					new OleDbParameter("@ImagePath", OleDbType.VarChar,255),
					new OleDbParameter("@id", OleDbType.Integer,4)};
            parameters[0].Value = model.Name;
            parameters[1].Value = model.Values;
            parameters[2].Value = model.Nodeid;
            parameters[3].Value = model.ParentID;
            parameters[4].Value = model.Type;
            parameters[5].Value = model.ImagePath;
            parameters[6].Value = model.id;

            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_dictionary ");
            strSql.Append(" where id=@id");
            OleDbParameter[] parameters = {
					new OleDbParameter("@id", OleDbType.Integer,4)
			};
            parameters[0].Value = id;

            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        /// <summary>
        /// 批量删除数据
        /// </summary>
        public bool DeleteList(string idlist)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from t_dictionary ");
            strSql.Append(" where id in (" + idlist + ")  ");
            int rows = DbHelperOleDb.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.DictionaryInfo GetModel(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,Name,Values,Nodeid,ParentID,Type,ImagePath from t_dictionary ");
            strSql.Append(" where id=@id");
            OleDbParameter[] parameters = {
					new OleDbParameter("@id", OleDbType.Integer,4)
			};
            parameters[0].Value = id;

            Model.DictionaryInfo model = new Model.DictionaryInfo();
            DataSet ds = DbHelperOleDb.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Name"] != null && ds.Tables[0].Rows[0]["Name"].ToString() != "")
                {
                    model.Name = ds.Tables[0].Rows[0]["Name"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Values"] != null && ds.Tables[0].Rows[0]["Values"].ToString() != "")
                {
                    model.Values = ds.Tables[0].Rows[0]["Values"].ToString();
                }
                if (ds.Tables[0].Rows[0]["Nodeid"] != null && ds.Tables[0].Rows[0]["Nodeid"].ToString() != "")
                {
                    model.Nodeid = int.Parse(ds.Tables[0].Rows[0]["Nodeid"].ToString());
                }
                if (ds.Tables[0].Rows[0]["ParentID"] != null && ds.Tables[0].Rows[0]["ParentID"].ToString() != "")
                {
                    model.ParentID = int.Parse(ds.Tables[0].Rows[0]["ParentID"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Type"] != null && ds.Tables[0].Rows[0]["Type"].ToString() != "")
                {
                    model.Type = ds.Tables[0].Rows[0]["Type"].ToString();
                }
                if (ds.Tables[0].Rows[0]["ImagePath"] != null && ds.Tables[0].Rows[0]["ImagePath"].ToString() != "")
                {
                    model.ImagePath = ds.Tables[0].Rows[0]["ImagePath"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select id,Name,Values,Nodeid,ParentID,Type,ImagePath ");
            strSql.Append(" FROM t_dictionary ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return DbHelperOleDb.Query(strSql.ToString());
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        public int GetRecordCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) FROM t_dictionary ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            object obj = DbHelperOleDb.GetSingle(strSql.ToString());
            if (obj == null)
            {
                return 0;
            }
            else
            {
                return Convert.ToInt32(obj);
            }
        }
        /// <summary>
        /// 分页获取数据列表
        /// </summary>
        public DataSet GetListByPage(string strWhere, string[] orderby, int startIndex, int endIndex)
        {
            //獲得查詢條數
            int count = endIndex - startIndex;
            //查詢條件
            string where = "";
            //內排序條件
            string Inorderby = "order by ID desc";
            //外排序條件
            string Outorderby = "order by ID ASC";
            if (!string.IsNullOrEmpty(strWhere.Trim()))
            {
                where += " where 1=1 " + strWhere;
                if (orderby.Length > 1)
                {
                    //0號位置存取排序字段
                    Inorderby = "order by " + orderby[0].Trim() + " " + orderby[1].Trim();
                    //1號位置為排序字段
                    if (orderby[1].Trim().ToLower().Equals("desc"))
                    {
                        Outorderby = "order by " + orderby[0].Trim() + " asc";
                    }
                    else
                    {
                        Outorderby = "order by " + orderby[0].Trim() + " desc";
                    }
                }
            }
            else
            {
                if (orderby.Length > 1)
                {
                    //0號位置存取排序字段
                    Inorderby = "order by " + orderby[0].Trim() + " " + orderby[1].Trim();
                    //1號位置為排序字段
                    if (orderby[1].Trim().ToLower().Equals("desc"))
                    {
                        Outorderby = "order by " + orderby[0].Trim() + " asc";
                    }
                    else
                    {
                        Outorderby = "order by " + orderby[0].Trim() + " desc";
                    }
                }
            }

            StringBuilder strSql = new StringBuilder();
            strSql.AppendFormat("select * from (select top {0} * from (select top {1} * from t_dictionary {2} {3} ) {4}) {3}", count, endIndex, where, Inorderby, Outorderby);

            return DbHelperOleDb.Query(strSql.ToString());
        }

        #endregion  Method
    }
}
